﻿Imports System.Data.SqlClient
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.SqlClient.SqlConnection
Imports System.Windows.Forms

Public Class FmTimHoaDon

    Private Sub BtThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtThoat.Click
        Me.Close()
    End Sub

    Public Sub getcb3()
        Dim sql4 As String
        sql4 = "select SOHD from HOADON "
        XdKetNoi()
        Dim dancc As New SqlDataAdapter(sql4, KetNoi)
        Dim dsncc As New DataSet
        dancc.Fill(dsncc, "HOADON")
        Dim datable As New DataTable()
        datable.Columns.Add("SOHD", GetType(System.String))
        Dim droldRow As DataRow
        Dim drnewRow As DataRow
        For Each droldRow In dsncc.Tables("HOADON").Rows()
            drnewRow = datable.NewRow()
            drnewRow("SOHD") = droldRow("SOHD")
            datable.Rows.Add(drnewRow)
        Next
        CbTimSoHD.DropDownStyle = ComboBoxStyle.DropDownList
        With CbTimSoHD
            .DataSource = datable
            .DisplayMember = "Description"
            .ValueMember = "SOHD"


        End With

        KetNoi.Close()


    End Sub

    Public Sub FillDataGridView1()
        XdKetNoi()
        Dim sql4 As String


        If (CbTimSoHD.Text <> "") Then
            Try
                sql4 = "select d.SOHD,d.NGAYLAP,e.TENKH,e.DIACHI,e.SDT,e.MASOTHUE,e.SOTK,a.MAHANG, b.TENHANG,a.SLBAN,a.DGBAN,(a.SLBAN * a.DGBAN ) as THANHTIEN ,d.VAT,d.TONGTIEN from (((CHITIETHOADON a inner join HANGHOA b on a.MAHANG=b.MAHANG) inner join HOADON d on a.SOHD=d.SOHD)inner join KHACHHANG e on d.MAKH=e.MAKH )WHERE d.SOHD='" & CbTimSoHD.Text & "'"
                Dim daCtiet As New SqlDataAdapter(sql4, KetNoi)
                'khoi tao dataset
                Dim dsCtiet As New DataSet
                ''dien du lieu vao dataset
                daCtiet.Fill(dsCtiet)

                Dim i As Integer
                i = dsCtiet.Tables(0).Rows.Count

                If i <> 0 Then
                    Me.DataGridView1.DataSource = dsCtiet.Tables(0)
                Else
                    MessageBox.Show("Không tìm thấy")
                End If

                KetNoi.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        Else
            MsgBox("Bạn vui lòng chọn số hóa đơn")
        End If


    End Sub

    Public Sub getcb4()
        Dim sql4 As String
        sql4 = "select TENKH from KHACHHANG "
        XdKetNoi()
        Dim dancc As New SqlDataAdapter(sql4, KetNoi)
        Dim dsncc As New DataSet
        dancc.Fill(dsncc, "KHACHHANG")
        Dim datable As New DataTable()
        datable.Columns.Add("TENKH", GetType(System.String))
        Dim droldRow As DataRow
        Dim drnewRow As DataRow
        For Each droldRow In dsncc.Tables("KHACHHANG").Rows()
            drnewRow = datable.NewRow()
            drnewRow("TENKH") = droldRow("TENKH")
            datable.Rows.Add(drnewRow)
        Next
        CbTimTenKH.DropDownStyle = ComboBoxStyle.DropDownList
        With CbTimTenKH
            .DataSource = datable
            .DisplayMember = "Description"
            .ValueMember = "TENKH"


        End With


        KetNoi.Close()

    End Sub


    Public Sub FillDataGridView3()
        XdKetNoi()
        Dim sql4 As String


        If (CbTimNgayLap.Text <> "") Then
            Try
                sql4 = "select d.SOHD,d.NGAYLAP,e.TENKH,e.DIACHI,e.SDT,e.MASOTHUE,e.SOTK,a.MAHANG, b.TENHANG,a.SLBAN,a.DGBAN,(a.SLBAN * a.DGBAN ) as THANHTIEN ,d.VAT,d.TONGTIEN from (((CHITIETHOADON a inner join HANGHOA b on a.MAHANG=b.MAHANG) inner join HOADON d on a.SOHD=d.SOHD)inner join KHACHHANG e on d.MAKH=e.MAKH )WHERE d.NGAYLAP='" & CbTimNgayLap.Text & "'"


                Dim daCtiet As New SqlDataAdapter(sql4, KetNoi)
                'khoi tao dataset
                Dim dsCtiet As New DataSet
                ''dien du lieu vao dataset
                daCtiet.Fill(dsCtiet)




                Dim i As Integer
                i = dsCtiet.Tables(0).Rows.Count

                If i <> 0 Then
                    Me.DataGridView1.DataSource = dsCtiet.Tables(0)
                Else
                    MessageBox.Show("Không tìm thấy")
                End If

                KetNoi.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        Else
            MsgBox("Bạn vui lòng chọn ngày lập hóa đơn")
        End If


    End Sub

    Public Sub getcb5()
        Dim sql4 As String
        sql4 = "select NGAYLAP from HOADON "
        XdKetNoi()
        Dim dancc As New SqlDataAdapter(sql4, KetNoi)
        Dim dsncc As New DataSet
        dancc.Fill(dsncc, "HOADON")
        Dim datable As New DataTable()
        datable.Columns.Add("NGAYLAP", GetType(System.String))
        Dim droldRow As DataRow
        Dim drnewRow As DataRow
        For Each droldRow In dsncc.Tables("HOADON").Rows()
            drnewRow = datable.NewRow()
            drnewRow("NGAYLAP") = droldRow("NGAYLAP")
            datable.Rows.Add(drnewRow)
        Next
        CbTimNgayLap.DropDownStyle = ComboBoxStyle.DropDownList
        With CbTimNgayLap
            .DataSource = datable
            .DisplayMember = "Description"
            .ValueMember = "NGAYLAP"


        End With

        KetNoi.Close()

    End Sub

    Public Sub FillDataGridView2()
        XdKetNoi()
        Dim sql4 As String


        If (CbTimTenKH.Text <> "") Then
            Try
                sql4 = "select d.SOHD,d.NGAYLAP,e.TENKH,e.DIACHI,e.SDT,e.MASOTHUE,e.SOTK,a.MAHANG, b.TENHANG,a.SLBAN,a.DGBAN,(a.SLBAN * a.DGBAN ) as THANHTIEN ,d.VAT,d.TONGTIEN from (((CHITIETHOADON a inner join HANGHOA b on a.MAHANG=b.MAHANG) inner join HOADON d on a.SOHD=d.SOHD)inner join KHACHHANG e on d.MAKH=e.MAKH )WHERE e.TENKH=N'" & CbTimTenKH.Text & "'"


                Dim daCtiet As New SqlDataAdapter(sql4, KetNoi)
                'khoi tao dataset
                Dim dsCtiet As New DataSet
                ''dien du lieu vao dataset
                daCtiet.Fill(dsCtiet)




                Dim i As Integer
                i = dsCtiet.Tables(0).Rows.Count

                If i <> 0 Then
                    Me.DataGridView1.DataSource = dsCtiet.Tables(0)
                Else
                    MessageBox.Show("Không tìm thấy")
                End If

                KetNoi.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        Else
            MsgBox("Bạn vui lòng chọn tên khách hàng")
        End If


    End Sub

    Private Sub FmTimHoaDon_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        XdKetNoi()

        Dim dieuphoi As New SqlDataAdapter("Select * from HOADON", KetNoi)

        Dim DTKH As New DataTable
        dieuphoi.Fill(DTKH)
        KetNoi.Close()
        DataGridView1.DataSource = DTKH
        BindingSource1.DataSource = DTKH
        DataGridView1.DataSource = BindingSource1

        getcb3()
        getcb4()
        getcb5()
        KetNoi.Close()

    End Sub

    Private Sub BtTimSoHD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtTimSoHD.Click
        FillDataGridView1()
    End Sub

    Private Sub BtTimNgayLap_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtTimNgayLap.Click
        FillDataGridView3()
    End Sub

    Private Sub BtTimTenKH_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtTimTenKH.Click
        FillDataGridView2()
    End Sub
End Class